#!/usr/bin/env python
# coding=utf-8
"""
@file: df_util.py
@contact: bianzhiwei@iyoujia.com
@time   : 2019/3/29 17:52 
@Desc   :
"""
import json
import logging
import records
import pymongo
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import pymongo


class MySQLHelper:
    """
    mysql 的封装
    """

    def __init__(self, host, user, pwd, db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.port = 3306
        self.encoding = 'utf8'

    def connect(self):
        self.conn = pymysql.connect(self.host, self.user, self.pwd, self.db, self.port, charset='utf8',
                                    use_unicode=False)
        return self.conn.cursor()

    def insert(self, sql):
        """
        insert update delete 操作
        :param sql:
        :return:
        """
        cur = self.connect()
        cur.execute(sql)
        cur.close()
        self.conn.commit()
        self.conn.close()

    def find_all(self, sql):
        """
        查找全部
        :param sql:
        :return:
        """
        cur = self.connect()
        cur.execute(sql)
        result = cur.fetchall()
        cur.close()
        self.conn.close()
        return result

    def find_all_as_dict_low(self, sql):
        """
        执行查询语句, 返回的每条记录是一个字典类型
        """
        try:
            cur = self.connect()
            cur.execute(sql)
            resList = cur.fetchall()
            columns = [column[0] for column in cur.description]

            results = []
            for row in resList:
                results.append(dict(zip(columns, list(map(self.bit_to_string, row)))))
            cur.close()
            # 查询完毕后必须关闭连接
            self.conn.close()
            return results
        except Exception as e:
            print(e)
            return None

    def bit_to_string(self, bit_text):
        """
        bit 改变成为 str
        :param bit_text:
        :return:
        """
        try:
            bit_text = str(bit_text, encoding='utf8').replace("'", '"')
            return bit_text
        except:
            return bit_text


try:
    youjia_13_jingdui_db = create_engine("mysql+pymysql://{dbname}:{password}:@{ip}/youjia_report?charset=utf8")
except Exception as e:
    logging.exception(e)
try:
    youjia_13_sjz_db = create_engine("mysql+pymysql://{db_name}:{password}@{ip}/youjia?charset=utf8")
except Exception as e:
    logging.exception(e)
try:
    youjia_26_db = create_engine("mysql+pymysql://bianzhiwei:yeO4FKAIleCBiIZ5@{ip}/youjia_report?charset=utf8")
except Exception as e:
    logging.exception(e)
try:
    db_youjia_url = 'mysql+pymysql://{db_name}:{password}@{ip}/youjia'
except Exception as e:
    logging.exception(e)
try:
    con_youjia = pymysql.connect(host='{ip}', port=3306, user='{db_name}', password='{password}',
                                 db='youjia', charset='utf8')
except Exception as e:
    logging.exception(e)
# # mysql+pymysql://spider:IBtOJLf4D6DVBwZa@cityhome-mysql-slave.ccbop3bvm4yv.rds.cn-north-1.amazonaws.com.cn/airbnb'
# try:
#     con_cityhome = pymysql.connect(host='cityhome-mysql-slave.ccbop3bvm4yv.rds.cn-north-1.amazonaws.com.cn', port=3306,
#                                    user='spider', password='IBtOJLf4D6DVBwZa',
#                                    db='airbnb', charset='utf8')
# except Exception as e:
#     logging.exception(e)
try:

    con_tpp = pymysql.connect(host='{ip}', port=3306, user='{db_name}', password='{password}',
                              db='youjia_tpp', charset='utf8')
except Exception as e:
    logging.exception(e)
try:

    con_report = pymysql.connect(host='{ip}', port=3306, user='{db_name}', password='{password}',
                                 db='youjia_report', charset='utf8')
except Exception as e:
    logging.exception(e)
try:
    con_test = pymysql.connect(host='{ip}', port=3306, user='bianzhiwei', password='yeO4FKAIleCBiIZ5',
                               db='youjia_report', charset='utf8')
except Exception as e:
    logging.error(e)

mongodb_url_ = 'mongodb://data:data_2018@{ip}:27017/'

mongodb_test_url_ = 'mongodb://admin:BaTxxN4oaRhjsZNE@192.168.80.23:27017/'

# 城宿数据库
try:
    con_mysql_cityhome = MySQLHelper(host='cityhome-mysql-slave.ccbop3bvm4yv.rds.cn-north-1.amazonaws.com.cn',
                                     user='spider',
                                     pwd='IBtOJLf4D6DVBwZa', db='airbnb')
except Exception as e:
    logging.error(e)
# 有家youjia正式库
try:
    con_mysql_youjia = MySQLHelper(host='{ip}', user='{db_name}', pwd='{password}',
                                   db='youjia')
except Exception as e:
    logging.error(e)

# 有家tpp正式库
try:
    con_mysql_tpp = MySQLHelper(host='{ip}', user='{db_name}', pwd='{password}',
                                db='youjia_tpp')
except Exception as e:
    logging.error(e)

# 有家report正式库
try:
    con_mysql_report = MySQLHelper(host='{ip}', user='{db_name}', pwd='{password}',
                                   db='youjia_report')
except Exception as e:
    logging.error(e)

# 有家report测试库
try:
    con_mysql_report_test = MySQLHelper(host='{ip}', user='bianzhiwei', pwd='yeO4FKAIleCBiIZ5',
                                        db='youjia_report')
except Exception as e:
    logging.error(e)

# 有家mongodb

try:
    con_mongodb_con = pymongo.MongoClient(host='{ip}', port=27017, username='data',
                                          password='data_2018')
except Exception as e:
    logging.error(e)


def df_to_table(table_name=None, df=None, primary_key=None, db_name=None):
    """
    将 df 写入数据库中

    INSERT INTO db_name.table_name (a,b,c) VALUES (a1,b1,c1),(a2,b2,c2) on duplicate key update c = values(c)

    pandas.DataFrame 将 数据插入库中
    :param table_name:  表名
    :param df:  pandas.DataFrame
    :param primary_key:  主键 唯一索引
    :param db_name:  库名
    :return:
    """

    if table_name is None or table_name == "":
        raise ValueError('表名 "table_name" 不能为空')
    if df is None or len(df) == 0:
        raise ValueError('pandas.DataFrame "df" 不能为空')
    if primary_key is None or len(primary_key) == 0:
        raise ValueError('主键 唯一索引 "primary_key"  不能为空')

    if isinstance(primary_key, str):
        primary_key = [primary_key]
    key_tuple = tuple(primary_key)

    df_columns = list(df.columns)

    for key in df_columns:
        # todo 没写完呢
        pass
        # column_list.pop(key)


def get_connection(test=False):
    """
    获取数据库连接
    :return:
    """

    db_name, db_pwd = '{db_name}', '{password}'
    db_host, db_table = '{ip}', 'youjia_report'

    if test:
        db_test_name, db_test_pwd = 'bianzhiwei', 'yeO4FKAIleCBiIZ5'
        db_test_host, db_test_table = '{ip}', 'youjia_report'
        return records.Database('mysql+pymysql://%s:%s@%s/%s?charset=utf8' % (db_test_name, db_test_pwd,
                                                                              db_test_host, db_test_table))

    return records.Database('mysql+pymysql://%s:%s@%s/%s?charset=utf8' % (db_name, db_pwd, db_host, db_table))


def get_yj_base_db_conn():
    """
    有家主库 慎用 慎用
    :return:
    todo 慎用 慎用 慎用 慎用 慎用 慎用 慎用 慎用
    """
    return records.Database('mysql+pymysql://youjia_w:4#bdcb@mhgvc@172.16.10.11/youjia?charset=utf8mb4')
    # db_test_name, db_test_pwd = 'bianzhiwei', 'yeO4FKAIleCBiIZ5'
    # db_test_host, db_test_table = '{ip}', 'youjia_report'
    # return records.Database('mysql+pymysql://%s:%s@%s/%s?charset=utf8' % (db_test_name, db_test_pwd,
    #                                                                       db_test_host, db_test_table))


def records_select(sql):
    """
    查询sql 转为 list   数据格式为 [{k:v},{k1,v1},{k2,v2}]
    :param sql:
    :return:
    """
    with get_connection() as conn:
        try:
            df = conn.query(sql).export("df")
            # orient 的参数{‘split’,’records’,’index’,’columns’,’values’}
            # 将 df 每个数转为数字类型
            df = df.apply(pd.to_numeric, errors='ignore')
            result_json = json.loads(df.to_json(orient='records'))
            return result_json
        except Exception as msg:
            logging.warning(sql)
            logging.exception(msg)
            return []


def records_select_df(sql):
    """
    利用 records 查询数据
    :param sql:
    :return:
    """
    with get_connection() as conn:
        try:
            df = conn.query(sql).export("df")
            # orient 的参数{‘split’,’records’,’index’,’columns’,’values’}
            # 将 df 每个数转为数字类型
            # df = df.apply(pd.to_numeric, errors='ignore')
            logging.info('查询的数据shape = {}'.format(df.shape))
            return df
        except Exception as msg:
            logging.warning(sql)
            logging.exception(msg)


def mongodb_conn(base_db, set_name, url_=mongodb_test_url_):
    """
    :param base_db: db名 eg:tujia
    :param set_name: collection 名 eg city
    :param url_:  默认是测试库
    :return:
    """
    mongo_client_ = pymongo.MongoClient(url_)
    db = mongo_client_[base_db]  # 连接mydb数据库，没有则自动创建
    return db[set_name]


def get_execute_conn(host, user, password, db, port=3306, charset='utf8'):
    """

    :param host:
    :param user:
    :param password:
    :param db:
    :param port:
    :param charset:
    :return:
    """
    return pymysql.connect(host=host, port=port, user=user, passwd=password, db=db, charset=charset)


def execute_sql(sql_tmp, param, host, user, password, db, port=3306, charset='utf8'):
    """
    执行sql
    :param sql_tmp: sql 模板 eg:
    INSERT INTO `youjia_report`.`wh_house_calendar`(`house_id`,`dt`,`online_stock`,
                              create_time,modify_time ) VALUES (%s ,%s ,%s ,%s ,%s),
                                                               (%s ,%s ,%s ,%s ,%s),
                                                                (%s ,%s ,%s ,%s ,%s)
                              on duplicate key update `online_stock` = values(online_stock),
                              modify_time=values(modify_time)
    其中 每个  (%s ,%s ,%s ,%s ,%s), 为一行`house_id`,`dt`,`online_stock`, create_time,modify_time 数据
    :param param: sql要插入的参数list：   [[a1, b1, c1],
                                        [a2, b2, c2],
                                        [a3, b3, c4]]
    :param args:
          host: 数据库地址
          user: 数据库用户名
          password: 数据库密码
          db: 数据库名
          port: 端口
          charset: 编码
    :param sql_tmp:
    :param param:
    :param host:
    :param user:
    :param password:
    :param db:
    :param port:
    :param charset:
    :return:
    """
    # todo 处理之后要换成线上的
    with get_execute_conn(host=host, port=port, user=user, password=password, db=db, charset=charset)as conn:
        # with get_connection() as conn:
        try:
            conn.execute(sql_tmp, param)
        except Exception as msg:
            logging.warning(sql_tmp)
            logging.warning(param)
            logging.exception(msg)


if __name__ == '__main__':
    import pandas as pd

    
    print('')
